package data.dao.daoImplementation;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import data.dao.UserDAOException;
import data.dao.UserTypeDAO;
import data.dto.userTypeDTO;
import data.searchdto.UserTypeSearchDTO;

public class UserTypeDAOImpl implements UserTypeDAO {

	@Override
	public userTypeDTO findUserType(UserTypeSearchDTO usertypesearchdto)
			throws UserDAOException {
		userTypeDTO temp = null;
		ResultSet result = null;
		PreparedStatement stmt = null;

		try {
			String sql = searchMatching(usertypesearchdto);
			stmt = util.Database.getInstance().getConnection().prepareStatement(sql);
			result = util.Database.getInstance().executeQuery(stmt);

			while (result.next()) {
				temp = new userTypeDTO();

				temp.setId(result.getShort("id"));
				temp.setName(result.getString("name"));
				temp.setAnnualLeave(result.getShort("annualleave"));
				temp.setMedicalLeave(result.getShort("medicalleave"));
				temp.setCompensationLeave(result.getDouble("compensationleave"));
				//temp.setCreatedBy(result.getShort("createdBy"));
				//temp.setCreatedOn(result.getTimestamp("createdOn"));
				//temp.setModifiedBy(result.getInt("modifiedBy"));
				//temp.setModifiedOn(result.getTimestamp("modifiedOn"));
				temp.setStatus(result.getShort("status"));
			}

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (result != null)
				try {
					result.close();

					if (stmt != null)
						stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

		return temp;
	}

	@Override
	public ArrayList<userTypeDTO> findAllUserType() throws UserDAOException {
		String sql = "SELECT * FROM usertype WHERE isactive = 1 ORDER BY id ASC ";
		ArrayList<userTypeDTO> searchResults = null;
		try {
			searchResults = listQuery(util.Database.getInstance().getConnection()
					.prepareStatement(sql));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return searchResults;
	}

	@Override
	public void insertUserType(userTypeDTO valueObject) throws UserDAOException {
		String sql = "";
		PreparedStatement stmt = null;

		try {
			sql = "INSERT INTO usertype ( name, annualleave, "
					+ "medicalleave, compensationleave, "
					+ "status,isactive) VALUES (?, ?, ?, ?, ?,1) ";
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			//stmt.setInt(1, valueObject.getId());
			stmt.setString(1, valueObject.getName());
			stmt.setShort(2, valueObject.getAnnualLeave());
			stmt.setShort(3, valueObject.getMedicalLeave());
			stmt.setDouble(4, valueObject.getCompensationLeave());
			//stmt.setInt(5, util.CurrentUser.getInstance().getUserId());
			//stmt.setTimestamp(6, util.CurrentDate.getCurrentTimeStamp());
			//stmt.setInt(7, util.CurrentUser.getInstance().getUserId());
			//stmt.setTimestamp(8, util.CurrentDate.getCurrentTimeStamp());
			stmt.setInt(5, valueObject.getStatus());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	@Override
	public void updateUserType(userTypeDTO valueObject) throws UserDAOException {
		String sql = "UPDATE usertype SET name = ?, annualleave = ?, medicalleave = ?, "
				+ "compensationleave = ?, "
				+ "status = ? WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setString(1, valueObject.getName());
			stmt.setShort(2, valueObject.getAnnualLeave());
			stmt.setShort(3, valueObject.getMedicalLeave());
			stmt.setDouble(4, valueObject.getCompensationLeave());
			//stmt.setInt(5, util.CurrentUser.getInstance().getUserId());
			//stmt.setTimestamp(6, util.CurrentDate.getCurrentTimeStamp());
			stmt.setInt(5, valueObject.getStatus());

			stmt.setInt(6, valueObject.getId());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	@Override
	public void deleteUserType(userTypeDTO User) throws NotFoundException,
			SQLException {

		String sql = "UPDATE usertype SET isactive = 0 WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, User.getId());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	protected ArrayList<userTypeDTO> listQuery(PreparedStatement stmt)
			throws SQLException {

		ArrayList<userTypeDTO> searchResults = new ArrayList<userTypeDTO>();
		ResultSet result = null;

		try {
			result = util.Database.getInstance().executeQuery(stmt);

			while (result.next()) {
				userTypeDTO temp = new userTypeDTO();

				temp.setId(result.getShort("id"));
				temp.setName(result.getString("name"));
				temp.setAnnualLeave(result.getShort("annualleave"));
				temp.setMedicalLeave(result.getShort("medicalleave"));
				temp.setCompensationLeave(result.getDouble("compensationleave"));
				//temp.setCreatedBy(result.getShort("createdBy"));
				//temp.setCreatedOn(result.getTimestamp("createdOn"));
				//temp.setModifiedBy(result.getInt("modifiedBy"));
				//temp.setModifiedOn(result.getTimestamp("modifiedOn"));
				temp.setStatus(result.getShort("status"));

				searchResults.add(temp);
			}

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (result != null)
				try {
					result.close();

					if (stmt != null)
						stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

		return searchResults;
	}
	
	 public String searchMatching(UserTypeSearchDTO valueObject) throws SQLException {

         boolean first = true;
         StringBuffer sql = new StringBuffer("SELECT * FROM usertype WHERE isactive = 1 AND 1=1 ");

         if (valueObject.getId() != 0) {
             if (first) { first = false; }
             sql.append("AND id = ").append(valueObject.getId()).append(" ");
         }

         if (valueObject.getName() != null) {
             if (first) { first = false; }
             sql.append("AND name LIKE '").append(valueObject.getName()).append("%' ");
         }

         sql.append("ORDER BY id ASC ");

         return sql.toString();
   }


}
